using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Collections;

	/// <summary>
	/// Summary description for Database.
	/// </summary>
public class Database : IDisposable
{
    #region Variables & Properties
    public const int DBEXCEPTION = -9;

    private string _connectionString;
    public string ConnectionString
    {
        get { return _connectionString; }
    }

    private int _commandTimeout = 36000;
    public int CommandTimeout
    {
        get { return _commandTimeout; }
    }

    private string _errorMessage = string.Empty;
    public string ErrorMessage
    {
        get { return _errorMessage; }
    }
    private bool _throwException = true;
    public bool ThrowException
    {
        get { return this._throwException; }
        set { this._throwException = value; }
    }

    //private bool _Debug = true;
    //public bool Debug
    //{
    //    get { return this._Debug; }
    //    set { this._Debug = value; }
    //}

    private SqlCommand command = null;
    private SqlConnection connection = null;

    #endregion

    #region Constructor

    public Database(string sConnectionString, int iCommandTimeout)
    {
        this._connectionString = sConnectionString;
        this._commandTimeout = iCommandTimeout;
    }
    public Database(string sConnectionString)
    {
        this._connectionString = sConnectionString;
    }

    public Database(string sServer, string sDatabase, string sUsername, string sPassword)
        : this("server=" + sServer + ";initial catalog=" + sDatabase + ";uid=" + sUsername + ";pwd=" + sPassword)
    {
    }
    #endregion

    #region Execute Query
    /// <summary>
    /// Thuc hien truy van database.
    /// </summary>
    /// <param name="strConnection">Chuoi connection tao boi phuong thuc CreateConnectionString.
    /// Dung de thuc hien ket noi database.</param>
    /// <param name="sqlSelect">Cau truy van.</param>
    /// <returns></returns>
    public DataSet ExecuteQuery(string sqlSelect)
    {
        try
        {
            this.createSQLCommand(sqlSelect);
            SqlDataAdapter adapter = new SqlDataAdapter(this.command);
            DataSet ds = new DataSet();
            //adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            adapter.Fill(ds);
            adapter.Dispose();
            return ds;
        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
            return null;
        }
    }
    public DataSet ExecuteQuery(string sqlSelect, int currPage, int pageSize)
    {
        try
        {
            this.createSQLCommand(sqlSelect);
            SqlDataAdapter adapter = new SqlDataAdapter(this.command);
            DataSet ds = new DataSet();
            if (currPage == 0 || pageSize == 0)
            {
                adapter.Fill(ds);
            }
            else
            {
                adapter.Fill(ds, (currPage - 1) * pageSize, pageSize, "ds");
            }            
            adapter.Dispose();
            return ds;
        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
            return null;
        }
    }

    /// <summary>
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="paramNames"></param>
    /// <param name="paramValues"></param>
    /// <returns></returns>
    public DataSet ExecuteQueryWithParam(string sql, string[] paramNames, object[] paramValues)
    {
        this.checkInvalidParam(paramNames, paramValues);
        try
        {
            this.createSQLCommand(sql, paramNames, paramValues);
            SqlDataAdapter adapter = new SqlDataAdapter(this.command);
            //				adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;				
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            adapter.Dispose();
            return ds;
        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
            return null;
        }
    }

    public DataSet ExecuteQueryWithParam(string sql, string[] paramNames, object[] paramValues, int CurrPage, int PageSize)
    {
        this.checkInvalidParam(paramNames, paramValues);
        try
        {
            this.createSQLCommand(sql, paramNames, paramValues);
            SqlDataAdapter adapter = new SqlDataAdapter(this.command);
            //adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;				
            DataSet ds = new DataSet();
            if (CurrPage == 0 || PageSize == 0)
            {
                adapter.Fill(ds);
            }
            else
            {
                adapter.Fill(ds, (CurrPage - 1) * PageSize, PageSize, "ds");
            }            
            adapter.Dispose();
            return ds;
        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
            return null;
        }
    }

    /// <summary>
    /// </summary>
    /// <param name="strConnection">Chuoi connection tao boi phuong thuc CreateConnectionString.
    /// Dung de thuc hien ket noi database.</param>
    /// <param name="sqlSelect">Cau lenh SQL.</param>
    /// <returns></returns>
    public DataSet ExecuteQueryWithParam(string sql, string[] paramNames, SqlDbType[] paramTypes, object[] paramValues)
    {
        this.checkInvalidParam(paramNames, paramTypes, paramValues);
        try
        {
            this.createSQLCommand(sql, paramNames, paramTypes, paramValues);

            SqlDataAdapter adapter = new SqlDataAdapter(this.command);
            //adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            adapter.Dispose();

            return ds;
        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
            return null;
        }
    }

    /// <summary>
    /// Thuc cau lenh database.
    /// </summary>
    /// <param name="strConnection">Chuoi connection tao boi phuong thuc CreateConnectionString.
    /// Dung de thuc hien ket noi database.</param>
    /// <param name="sqlSelect">Cau lenh SQL.</param>
    /// <returns></returns>
    public int ExecuteNonQuery(string sqlSelect)
    {
        try
        {
            this.createSQLCommand(sqlSelect);
            this.connection.Open();
            int result = this.command.ExecuteNonQuery();
            this.connection.Close();
            return result;
        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
            return DBEXCEPTION;
        }
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="paramNames"></param>
    /// <param name="paramValues"></param>
    /// <returns></returns>
    public int ExecuteNonQueryWithParam(string sql, string[] paramNames, object[] paramValues)
    {
        this.checkInvalidParam(paramNames, paramValues);
        try
        {
            this.createSQLCommand(sql, paramNames, paramValues);
            this.connection.Open();
            int result = this.command.ExecuteNonQuery();
            this.connection.Close();
            return result;
        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
            return DBEXCEPTION;
        }
    }
    /// <summary>
    /// ExecuteNonQueryWithParam.
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="paramNames">Cau lenh sql co tham so(@).</param>
    /// <param name="paramTypes">Ten tham so(@sample).</param>
    /// <param name="paramValues">values.</param>
    public int ExecuteNonQueryWithParam(string sql, string[] paramNames, SqlDbType[] paramTypes, object[] paramValues)
    {
        this.checkInvalidParam(paramNames, paramTypes, paramValues);
        try
        {
            this.createSQLCommand(sql, paramNames, paramTypes, paramValues);
            this.connection.Open();
            int result = this.command.ExecuteNonQuery();
            this.connection.Close();
            return result;
        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
            return DBEXCEPTION;
        }
    }
    /// <summary>
    /// Thuc cau lenh database va tra ve gia tri dau tien.
    /// </summary>
    /// <param name="strConnection">Chuoi connection tao boi phuong thuc CreateConnectionString.
    /// Dung de thuc hien ket noi database.</param>
    /// <param name="sqlSelect">Cau lenh SQL.</param>
    /// <returns></returns>
    public Object ExecuteScalar(string sqlSelect)
    {
        try
        {
            this.createSQLCommand(sqlSelect);
            this.connection.Open();
            Object result = this.command.ExecuteScalar();
            this.connection.Close();
            return result;
        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
            return null;
        }
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="paramNames"></param>
    /// <param name="paramValues"></param>
    /// <returns></returns>
    public object ExecuteScalarWithParam(string sql, string[] paramNames, object[] paramValues)
    {
        this.checkInvalidParam(paramNames, paramValues);
        try
        {
            this.createSQLCommand(sql, paramNames, paramValues);
            this.connection.Open();
            object rs = this.command.ExecuteScalar();
            this.connection.Close();
            return rs;
        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
            return null;
        }
    }
    /// <summary>
    /// Thuc cau lenh database va tra ve gia tri dau tien.
    /// </summary>
    /// <param name="strConnection">Chuoi connection tao boi phuong thuc CreateConnectionString.
    /// Dung de thuc hien ket noi database.</param>
    /// <param name="sqlSelect">Cau lenh SQL.</param>
    /// <returns></returns>
    public object ExecuteScalarWithParam(string sql, string[] paramNames, SqlDbType[] paramTypes, object[] paramValues)
    {
        this.checkInvalidParam(paramNames, paramTypes, paramValues);

        try
        {
            this.createSQLCommand(sql, paramNames, paramTypes, paramValues);
            this.connection.Open();
            object rs = this.command.ExecuteScalar();
            this.connection.Close();
            return rs;
        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
            return null;
        }
    }
    #endregion Execute Query

    #region Execute Store
    public void ExecuteStoredProc(string storeName, Hashtable data)
    {
        this.ExecuteStoredProc(storeName, data, false);
    }
    public void ExecuteStoredProc(string storeName, Hashtable data, bool checkSchema)
    {
        if (data == null || this.IsEmpty(storeName))
            return;

        try
        {
            ArrayList schema = null;
            if (checkSchema)
            {
                schema = this.GetParamNamesArray(storeName);
                if (schema == null)
                    throw new Exception("Cannot check schema !");
            }
            this.createSQLCommand(storeName);
            this.command.CommandType = CommandType.StoredProcedure;
            string paramName = string.Empty;
            object value = null;
            int count = 0;
            foreach (string key in data.Keys)
            {
                if (checkSchema && !schema.Contains(key))
                    continue;
                value = data[key];
                value = value != null ? value : System.DBNull.Value;
                paramName = (key.IndexOf("@") >= 0 ? key : "@" + key);
                this.command.Parameters.Add(new SqlParameter(paramName, value));
                count++;
            }
            if (checkSchema && count < schema.Count)
            {
                string tmp = string.Empty;
                foreach (string param in schema)
                    if (!data.ContainsKey(param))
                        tmp += param + ",";
                throw new Exception("Database Alert: Not enough parameter(" + tmp.Trim(new char[] { ',' }) + ") to execute store proc !");
            }

            this.connection.Open();
            this.command.ExecuteNonQuery();
            this.command.Dispose();
            this.connection.Close();

        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
        }
    }
    public void ExecuteStoredProc(string storeName, string[] paramNames, object[] paramValues)
    {
        this.ExecuteStoredProc(storeName, paramNames, paramValues, false);
    }
    public void ExecuteStoredProc(string storeName, string[] paramNames, object[] paramValues, bool checkSchema)
    {
        if (paramNames == null || paramValues == null || this.IsEmpty(storeName)) return;
        int N = Math.Min(paramNames.Length, paramValues.Length);

        try
        {
            ArrayList schema = null;
            if (checkSchema)
            {
                schema = this.GetParamNamesArray(storeName);
                if (schema == null)
                    throw new Exception("Cannot check schema !");
            }

            this.createSQLCommand(storeName);
            this.command.CommandType = CommandType.StoredProcedure;
            int count = 0;
            for (int i = 0; i < N; i++)
            {
                if (checkSchema && !schema.Contains(paramNames[i]))
                    continue;
                SqlParameter param = new SqlParameter("@" + paramNames[i], (paramValues[i] != null) ? paramValues[i] : System.DBNull.Value);
                this.command.Parameters.Add(param);
                count++;
            }
            if (checkSchema && count < schema.Count)
            {
                string tmp = string.Empty;
                ArrayList tmpArray = new ArrayList(paramNames);
                foreach (string param in schema)
                    if (!tmpArray.Contains(param))
                        tmp += param + ",";
                throw new Exception("Database Alert: Not enough parameter(" + tmp.Trim(new char[] { ',' }) + ") to execute store proc !");
            }

            this.connection.Open();
            this.command.ExecuteNonQuery();
            this.connection.Close();

        }
        catch (Exception ex)
        {

            this.ErrorProccess(ex);
        }
    }
    public void ExecuteStoredProc(string storeName, string[] paramNames, SqlDbType[] paramTypes, object[] paramValues)
    {
        this.ExecuteStoredProc(storeName, paramNames, paramTypes, paramValues, false);
    }
    public void ExecuteStoredProc(string storeName, string[] paramNames, SqlDbType[] paramTypes, object[] paramValues, bool checkSchema)
    {
        if (paramNames == null || paramTypes == null || paramValues == null || this.IsEmpty(storeName)) return;
        int N = Math.Min(paramNames.Length, paramTypes.Length);
        N = Math.Min(N, paramValues.Length);

        try
        {
            ArrayList schema = null;
            if (checkSchema)
            {
                schema = this.GetParamNamesArray(storeName);
                if (schema == null)
                    throw new Exception("Cannot check schema !");
            }
            this.createSQLCommand(storeName);
            this.command.CommandType = CommandType.StoredProcedure;
            int count = 0;
            for (int i = 0; i < N; i++)
            {
                if (checkSchema && !schema.Contains(paramNames[i]))
                    continue;
                SqlParameter param = new SqlParameter("@" + paramNames[i], paramTypes[i]);
                param.Value = (paramValues[i] != null) ? paramValues[i] : System.DBNull.Value;
                this.command.Parameters.Add(param);
                count++;
            }
            if (checkSchema && count < schema.Count)
            {
                string tmp = string.Empty;
                ArrayList tmpArray = new ArrayList(paramNames);
                foreach (string param in schema)
                    if (!tmpArray.Contains(param))
                        tmp += param + ",";
                throw new Exception("Database Alert: Not enough parameter(" + tmp.Trim(new char[] { ',' }) + ") to execute store proc !");
            }
            this.connection.Open();
            this.command.ExecuteNonQuery();
            this.connection.Close();

        }
        catch (Exception ex)
        {

            this.ErrorProccess(ex);
        }
    }
    public object ExecuteStoredProcReturnObject(string storeName, Hashtable data)
    {
        return this.ExecuteStoredProcReturnObject(storeName, data, false);
    }
    public object ExecuteStoredProcReturnObject(string storeName, Hashtable data, bool checkSchema)
    {
        try
        {
            if (data == null || this.IsEmpty(storeName))
                throw new Exception("Invalid Argument");

            ArrayList schema = null;
            if (checkSchema)
            {
                schema = this.GetParamNamesArray(storeName);
                if (schema == null)
                    throw new Exception("Cannot check schema !");
            }
            this.createSQLCommand(storeName);
            this.command.CommandType = CommandType.StoredProcedure;
            object oValue = null;
            int count = 0;
            foreach (string key in data.Keys)
            {
                if (checkSchema && !schema.Contains("@" + key))
                    continue;
                oValue = data[key];
                oValue = oValue != null ? oValue : System.DBNull.Value;
                this.command.Parameters.Add(new SqlParameter("@" + key, oValue));
                count++;
            }
            if (checkSchema && count < schema.Count)
            {
                string tmp = string.Empty;
                foreach (string param in schema)
                    if (!data.ContainsKey(param))
                        tmp += param + ",";
                throw new Exception("Database Alert: Not enough parameter(" + tmp.Trim(new char[] { ',' }) + ") to execute store proc !");
            }
            SqlParameter returnParam = this.command.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
            returnParam.Direction = ParameterDirection.ReturnValue;

            this.connection.Open();
            this.command.ExecuteNonQuery();
            this.connection.Close();

            return returnParam.Value;
        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
            return null;
        }
    }
    public object ExecuteStoredProcReturnObject(string storeName, string[] paramNames, object[] paramValues)
    {
        return this.ExecuteStoredProcReturnObject(storeName, paramNames, paramValues, false);
    }
    /// <summary>
    /// ExecuteStoredProcReturnObject
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="paramNames"></param>
    /// <param name="paramValues"></param>
    /// <returns></returns>
    public object ExecuteStoredProcReturnObject(string storeName, string[] paramNames, object[] paramValues, bool checkSchema)
    {
        if (paramNames == null || paramValues == null || this.IsEmpty(storeName)) return null;
        int N = Math.Min(paramNames.Length, paramValues.Length);

        try
        {
            ArrayList schema = null;
            if (checkSchema)
            {
                schema = this.GetParamNamesArray(storeName);
                if (schema == null)
                    throw new Exception("Cannot check schema !");
            }
            this.createSQLCommand(storeName);
            this.command.CommandType = CommandType.StoredProcedure;
            int count = 0;
            for (int i = 0; i < N; i++)
            {
                if (checkSchema && !schema.Contains(paramNames[i]))
                    continue;
                SqlParameter param = new SqlParameter("@" + paramNames[i], (paramValues[i] != null) ? paramValues[i] : System.DBNull.Value);
                this.command.Parameters.Add(param);
                count++;
            }
            if (checkSchema && count < schema.Count)
            {
                string tmp = string.Empty;
                ArrayList tmpArray = new ArrayList(paramNames);
                foreach (string param in schema)
                    if (!tmpArray.Contains(param))
                        tmp += param + ",";
                throw new Exception("Database Alert: Not enough parameter(" + tmp.Trim(new char[] { ',' }) + ") to execute store proc !");
            }
            SqlParameter returnParam = this.command.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
            returnParam.Direction = ParameterDirection.ReturnValue;

            this.connection.Open();
            this.command.ExecuteNonQuery();
            this.command.Dispose();
            this.connection.Close();

            return returnParam.Value;
        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
            return null;
        }
    }
    public object ExecuteStoredProcReturnObject(string sql, string[] paramNames, SqlDbType[] paramTypes, object[] paramValues)
    {
        return ExecuteStoredProcReturnObject(sql, paramNames, paramTypes, paramValues, false);
    }
    /// <summary>
    /// ExecuteStoredProcReturnObject.
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="paramNames"></param>
    /// <param name="paramTypes"></param>
    /// <param name="paramValues"></param>
    /// <returns></returns>
    public object ExecuteStoredProcReturnObject(string storeName, string[] paramNames, SqlDbType[] paramTypes, object[] paramValues, bool checkSchema)
    {
        if (paramNames == null || paramTypes == null || paramValues == null) return null;
        int N = Math.Min(paramNames.Length, paramTypes.Length);
        N = Math.Min(N, paramValues.Length);

        try
        {
            ArrayList schema = null;
            if (checkSchema)
            {
                schema = this.GetParamNamesArray(storeName);
                if (schema == null)
                    throw new Exception("Cannot check schema !");
            }

            this.createSQLCommand(storeName);
            this.command.CommandType = CommandType.StoredProcedure;
            int count = 0;
            for (int i = 0; i < N; i++)
            {
                if (checkSchema && !schema.Contains(paramNames[i]))
                    continue;
                SqlParameter param = new SqlParameter("@" + paramNames[i], paramTypes[i]);
                param.Value = (paramValues[i] != null) ? paramValues[i] : System.DBNull.Value;
                this.command.Parameters.Add(param);
                count++;
            }
            if (checkSchema && count < schema.Count)
            {
                string tmp = string.Empty;
                ArrayList tmpArray = new ArrayList(paramNames);
                foreach (string param in schema)
                    if (!tmpArray.Contains(param))
                        tmp += param + ",";
                throw new Exception("Database Alert: Not enough parameter(" + tmp.Trim(new char[] { ',' }) + ") to execute store proc !");
            }
            SqlParameter returnParam = this.command.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
            returnParam.Direction = ParameterDirection.ReturnValue;

            this.connection.Open();
            this.command.ExecuteNonQuery();
            this.command.Dispose();
            this.connection.Close();

            return returnParam.Value;
        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
            return null;
        }
    }

    public DataSet ExecuteStoredProcReturnDataSet(string storeName, Hashtable data)
    {
        return this.ExecuteStoredProcReturnDataSet(storeName, data, false);
    }
    public DataSet ExecuteStoredProcReturnDataSet(string storeName, Hashtable data, bool checkSchema)
    {
        if (data == null || this.IsEmpty(storeName))
            throw new Exception("Invalid Argument");

        try
        {
            ArrayList schema = null;
            if (checkSchema)
            {
                schema = this.GetParamNamesArray(storeName);
                if (schema == null)
                    throw new Exception("Cannot check schema !");
            }
            this.createSQLCommand(storeName);
            this.command.CommandType = CommandType.StoredProcedure;
            string paramName = string.Empty;
            object value = null;
            int count = 0;
            foreach (string key in data.Keys)
            {
                if (checkSchema && !schema.Contains(key))
                    continue;
                value = data[key];
                value = value != null ? value : System.DBNull.Value;
                paramName = (key.IndexOf("@") >= 0 ? key : "@" + key);
                this.command.Parameters.Add(new SqlParameter(paramName, value));
                count++;
            }
            if (checkSchema && count < schema.Count)
            {
                string tmp = string.Empty;
                foreach (string param in schema)
                    if (!data.ContainsKey(param))
                        tmp += param + ",";
                throw new Exception("Database Alert: Not enough parameter(" + tmp.Trim(new char[] { ',' }) + ") to execute store proc !");
            }
            SqlDataAdapter adapter = new SqlDataAdapter(this.command);
            DataSet ds = new DataSet();

            this.connection.Open();
            adapter.Fill(ds);
            adapter.Dispose();
            this.command.Dispose();
            this.connection.Close();

            return ds;
        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
            return null;
        }
    }
    public DataSet ExecuteStoredProcReturnDataSet(string storeName, Hashtable data, out int returnValue, bool checkSchema)
    {
        if (data == null || storeName == string.Empty)
            throw new Exception("Invalid Argument");

        try
        {
            ArrayList schema = null;
            if (checkSchema)
            {
                schema = this.GetParamNamesArray(storeName);
                if (schema == null)
                    throw new Exception("Cannot check schema !");
            }
            SqlConnection con = new SqlConnection(this.ConnectionString);
            SqlCommand cmd = new SqlCommand(storeName, con);
            cmd.CommandType = CommandType.StoredProcedure;
            string paramName = string.Empty;
            object value = null;
            int count = 0;
            foreach (string key in data.Keys)
            {
                if (checkSchema && !schema.Contains(key))
                    continue;
                value = data[key];
                value = value != null ? value : System.DBNull.Value;
                paramName = (key.IndexOf("@") >= 0 ? key : "@" + key);
                cmd.Parameters.Add(new SqlParameter(paramName, value));
                count++;
            }
            if (checkSchema && count < schema.Count)
            {
                string tmp = string.Empty;
                foreach (string param in schema)
                    if (!data.ContainsKey(param))
                        tmp += param + ",";
                throw new Exception("Database Alert: Not enough parameter(" + tmp.Trim(new char[] { ',' }) + ") to execute store proc !");
            }
            SqlParameter returnParam = cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
            returnParam.Direction = ParameterDirection.ReturnValue;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            con.Open();
            adapter.Fill(ds);
            adapter.Dispose();
            cmd.Dispose();
            con.Close();

            returnValue = (int)returnParam.Value;
            return ds;
        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
            returnValue = -1;
            return null;
        }
    }
    public DataSet ExecuteStoredProcReturnDataSet(string sql, string[] paramNames, object[] paramValues)
    {
        return this.ExecuteStoredProcReturnDataSet(sql, paramNames, paramValues, false);
    }
    public DataSet ExecuteStoredProcReturnDataSet(string storeName, string[] paramNames, object[] paramValues, bool checkSchema)
    {
        if (paramNames == null || paramValues == null) return null;
        int N = Math.Min(paramNames.Length, paramValues.Length);

        try
        {
            ArrayList schema = null;
            if (checkSchema)
            {
                schema = this.GetParamNamesArray(storeName);
                if (schema == null)
                    throw new Exception("Cannot check schema !");
            }

            this.createSQLCommand(storeName);
            this.command.CommandType = CommandType.StoredProcedure;
            int count = 0;
            for (int i = 0; i < N; i++)
            {
                if (checkSchema && !schema.Contains(paramNames[i]))
                    continue;
                SqlParameter param = new SqlParameter("@" + paramNames[i], (paramValues[i] != null) ? paramValues[i] : System.DBNull.Value);
                this.command.Parameters.Add(param);
                count++;
            }
            if (checkSchema && count < schema.Count)
            {
                string tmp = string.Empty;
                ArrayList tmpArray = new ArrayList(paramNames);
                foreach (string param in schema)
                    if (!tmpArray.Contains(param))
                        tmp += param + ",";
                throw new Exception("Database Alert: Not enough parameter(" + tmp.Trim(new char[] { ',' }) + ") to execute store proc !");
            }
            SqlDataAdapter adapter = new SqlDataAdapter(this.command);
            DataSet ds = new DataSet();

            this.connection.Open();
            adapter.Fill(ds);
            adapter.Dispose();
            this.command.Dispose();
            this.connection.Close();

            return ds;
        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
            return null;
        }
    }
    public DataSet ExecuteStoredProcReturnDataSet(string sql, string[] paramNames, object[] paramValues, out int returnValue)
    {
        returnValue = -1;
        if (paramNames == null || paramValues == null) return null;
        int N = Math.Min(paramNames.Length, paramValues.Length);

        try
        {
            this.createSQLCommand(sql);
            this.command.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < N; i++)
            {
                SqlParameter param = new SqlParameter("@" + paramNames[i], (paramValues[i] != null) ? paramValues[i] : System.DBNull.Value);
                this.command.Parameters.Add(param);
            }
            SqlParameter returnParam = this.command.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
            returnParam.Direction = ParameterDirection.ReturnValue;
            SqlDataAdapter adapter = new SqlDataAdapter(this.command);
            DataSet ds = new DataSet();

            this.connection.Open();
            adapter.Fill(ds);
            adapter.Dispose();
            this.command.Dispose();
            this.connection.Close();
            returnValue = (int)returnParam.Value;

            return ds;
        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
            return null;
        }
    }

    public DataSet ExecuteStoredProcReturnDataSet(string sql, string[] paramNames, SqlDbType[] paramTypes, object[] paramValues)
    {
        return this.ExecuteStoredProcReturnDataSet(sql, paramNames, paramTypes, paramValues, false);
    }
    /// <summary>
    /// ExecuteStoredProcReturnDataSet.
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="paramNames"></param>
    /// <param name="paramTypes"></param>
    /// <param name="paramValues"></param>
    /// <returns></returns>
    public DataSet ExecuteStoredProcReturnDataSet(string storeName, string[] paramNames, SqlDbType[] paramTypes, object[] paramValues, bool checkSchema)
    {
        if (paramNames == null || paramTypes == null || paramValues == null) return null;
        int N = Math.Min(paramNames.Length, paramTypes.Length);
        N = Math.Min(N, paramValues.Length);

        try
        {
            ArrayList schema = null;
            if (checkSchema)
            {
                schema = this.GetParamNamesArray(storeName);
                if (schema == null)
                    throw new Exception("Cannot check schema !");
            }
            this.createSQLCommand(storeName);
            this.command.CommandType = CommandType.StoredProcedure;
            int count = 0;
            for (int i = 0; i < N; i++)
            {
                if (checkSchema && !schema.Contains(paramNames[i]))
                    continue;
                SqlParameter param = new SqlParameter("@" + paramNames[i], paramTypes[i]);
                param.Value = (paramValues[i] != null) ? paramValues[i] : System.DBNull.Value;
                this.command.Parameters.Add(param);
                count++;
            }
            if (checkSchema && count < schema.Count)
            {
                string tmp = string.Empty;
                ArrayList tmpArray = new ArrayList(paramNames);
                foreach (string param in schema)
                    if (!tmpArray.Contains(param))
                        tmp += param + ",";
                throw new Exception("Database Alert: Not enough parameter(" + tmp.Trim(new char[] { ',' }) + ") to execute store proc !");
            }
            SqlDataAdapter adapter = new SqlDataAdapter(this.command);
            DataSet ds = new DataSet();

            this.connection.Open();
            adapter.Fill(ds);
            adapter.Dispose();
            this.command.Dispose();
            this.connection.Close();

            return ds;
        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
            return null;
        }
    }
    /// <summary>
    /// ExecuteStoredProcReturnDataSet.
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="paramNames"></param>
    /// <param name="paramTypes"></param>
    /// <param name="paramValues"></param>
    /// <returns></returns>
    public DataSet ExecuteStoredProcReturnDataSet(string sql, string[] paramNames, SqlDbType[] paramTypes, object[] paramValues, out int returnValue)
    {
        returnValue = -1;
        if (paramNames == null || paramTypes == null || paramValues == null) return null;
        int N = Math.Min(paramNames.Length, paramTypes.Length);
        N = Math.Min(N, paramValues.Length);

        try
        {
            this.createSQLCommand(sql);
            this.command.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < N; i++)
            {
                SqlParameter param = new SqlParameter("@" + paramNames[i], paramTypes[i]);
                param.Value = (paramValues[i] != null) ? paramValues[i] : System.DBNull.Value;
                this.command.Parameters.Add(param);
            }
            SqlParameter returnParam = this.command.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
            returnParam.Direction = ParameterDirection.ReturnValue;
            SqlDataAdapter adapter = new SqlDataAdapter(this.command);
            DataSet ds = new DataSet();

            this.connection.Open();
            adapter.Fill(ds);
            adapter.Dispose();
            this.command.Dispose();
            this.connection.Close();
            returnValue = (int)returnParam.Value;

            return ds;
        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
            return null;
        }
    }
    #endregion Execute Store

    #region Auto
    public void ExecuteInsert(Hashtable data, string tableName)
    {
        this.ExecuteInsert(data, tableName, false);
    }
    /// <summary>
    /// ExecuteInsert.
    /// </summary>
    /// <param name="data"></param>
    /// <param name="tableName"></param>
    public void ExecuteInsert(Hashtable data, string tableName, bool checkSchema)
    {
        if (data == null || data.Count == 0 || this.IsEmpty(tableName))
            return;

        try
        {
            ArrayList schema = null;
            if (checkSchema)
            {
                schema = this.GetColumnNamesArray(tableName);
                if (schema == null)
                    throw new Exception("Cannot check schema !");
            }
            this.createSQLCommand(tableName);

            object value = null;
            ArrayList fields = new ArrayList();
            foreach (string key in data.Keys)
            {
                if (checkSchema && !schema.Contains(key))
                    continue;
                fields.Add(key);
                value = data[key];
                value = value != null ? value : System.DBNull.Value;
                this.command.Parameters.Add(new SqlParameter("@" + key, value));
            }

            this.command.CommandText = this.CreateInsertSql(fields, tableName);

            this.connection.Open();
            this.command.ExecuteNonQuery();
            this.command.Dispose();
            this.connection.Close();
        }
        catch (Exception ex)
        {

            this.ErrorProccess(ex);
        }
    }
    public bool ExecuteUpdate(Hashtable data, string tableName, string idField, object idValue)
    {
        return this.ExecuteUpdate(data, tableName, idField, idValue, false);
    }
    public bool ExecuteUpdate(Hashtable data, string tableName, string idField, object idValue, bool checkSchema)
    {
        if (data == null || data.Count == 0 || this.IsEmpty(tableName))
            return false;

        try
        {
            ArrayList schema = null;
            if (checkSchema)
            {
                schema = this.GetColumnNamesArray(tableName);
                if (schema == null)
                    throw new Exception("Cannot check schema !");
            }
            this.createSQLCommand(tableName);
            object value = null;
            ArrayList fields = new ArrayList();
            foreach (string key in data.Keys)
            {
                if (checkSchema && !schema.Contains(key))
                    continue;
                fields.Add(key);
                value = data[key];
                value = value != null ? value : System.DBNull.Value;
                this.command.Parameters.Add(new SqlParameter("@" + key, value));
            }
            this.command.Parameters.Add(new SqlParameter("@_" + idField, idValue));
            this.command.CommandText = this.CreateUpdateSql(fields, tableName, idField);

            this.connection.Open();
            object rs = this.command.ExecuteNonQuery();
            this.command.Dispose();
            this.connection.Close();

            return true;
        }
        catch (Exception ex)
        {
            this.ErrorProccess(ex);
            return false;
        }
    }
    #endregion Auto

    #region Utility
    internal DataTable GetSchema(string tableName)
    {
        string sql = "SELECT * FROM " + tableName;
        return this.GetSchemaOfSql(sql);
    }
    internal DataTable GetSchemaOfSql(string sql)
    {
        SqlDataAdapter adapter = new SqlDataAdapter(sql, this._connectionString);
        //adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
        DataTable schema = new DataTable();
        adapter.FillSchema(schema, SchemaType.Source);
        adapter.Dispose();
        return schema;
    }
    internal DataTable GetColumns(string objectName)
    {
        string sql = "SELECT a.* FROM syscolumns a,sysobjects b WHERE b.name='" + objectName + "' and a.id=b.id";
        DataSet ds = this.ExecuteQuery(sql);
        return (ds != null || ds.Tables.Count > 0) ? ds.Tables[0] : null;
    }
    internal ArrayList GetColumnNamesArray(string objectName)
    {
        DataTable tb = this.GetColumnNames(objectName);
        if (tb == null)
            return null;
        ArrayList ar = new ArrayList(tb.Rows.Count);
        foreach (DataRow row in tb.Rows)
            ar.Add(row[0].ToString());
        return ar;
    }
    internal ArrayList GetParamNamesArray(string objectName)
    {
        DataTable tb = this.GetColumnNames(objectName);
        if (tb == null)
            return null;
        ArrayList ar = new ArrayList(tb.Rows.Count);
        string tmp = string.Empty;
        foreach (DataRow row in tb.Rows)
        {
            tmp = row[0].ToString();
            if (tmp[0] == '@') ar.Add(tmp);
        }
        return ar;
    }
    internal DataTable GetColumnNames(string objectName)
    {
        string sql = "SELECT a.name FROM syscolumns a,sysobjects b WHERE b.name='" + objectName + "' and a.id=b.id";
        DataSet ds = this.ExecuteQuery(sql);
        return (ds != null || ds.Tables.Count > 0) ? ds.Tables[0] : null;
    }
    private string CreateInsertSql(IEnumerable fields, string tableName)
    {
        StringBuilder s1 = new StringBuilder(500);
        s1.Append("INSERT INTO " + tableName + "(");
        StringBuilder s2 = new StringBuilder(500);
        s2.Append(" VALUES(");
        bool first = true;
        foreach (string field in fields)
        {
            if (first)
                first = false;
            else
            {
                s1.Append(",");
                s2.Append(",");
            }
            s1.Append(field);
            s2.Append("@" + field);
        }
        s1.Append(")");
        s2.Append(")");
        return s1.ToString() + s2.ToString();
    }
    private string CreateUpdateSql(IEnumerable fields, string tableName, string idField)
    {
        StringBuilder s = new StringBuilder(500);
        s.Append("UPDATE " + tableName + " SET ");
        bool first = true;
        foreach (string field in fields)
        {
            if (first)
                first = false;
            else
                s.Append(",");
            s.Append(field + "=@" + field);
        }
        s.Append(" WHERE " + idField + "=@_" + idField);
        return s.ToString();
    }
    public static object ProtectNull(object value)
    {
        return value == null ? (object)System.DBNull.Value : value;
    }
    private void createSQLConnection()
    {
        this.connection = new SqlConnection(this._connectionString);
    }
    private void createSQLCommand(string sql)
    {
        this.createSQLConnection();
        this.command = new SqlCommand(sql, this.connection);
        this.command.CommandTimeout = _commandTimeout;
    }
    private void createSQLCommand(string sql, string[] paramNames, object[] paramValues)
    {
        this.connection = new SqlConnection(this._connectionString);
        this.command = new SqlCommand(sql, this.connection);
        this.command.CommandTimeout = _commandTimeout;
        for (int i = 0; i < paramNames.Length; i++)
        {
            SqlParameter param = new SqlParameter("@" + paramNames[i], (paramValues[i] != null) ? paramValues[i] : System.DBNull.Value);
            this.command.Parameters.Add(param);
        }
    }
    private void createSQLCommand(string sql, string[] paramNames, SqlDbType[] paramTypes, object[] paramValues)
    {
        this.connection = new SqlConnection(this._connectionString);
        this.command = new SqlCommand(sql, this.connection);
        this.command.CommandTimeout = _commandTimeout;
        for (int i = 0; i < paramNames.Length; i++)
        {
            SqlParameter param = new SqlParameter("@" + paramNames[i], paramTypes[i]);
            param.Value = (paramValues[i] != null) ? paramValues[i] : System.DBNull.Value;
            command.Parameters.Add(param);
        }
    }
    private void checkInvalidParam(string[] paramNames, object[] paramValues)
    {
        bool isValid = true;
        if (paramNames == null || paramValues == null) isValid = false;
        int N = Math.Min(paramNames.Length, paramValues.Length);
        if (N == 0) isValid = false;
        if (!isValid)
            throw new Exception("Database: Invalid Length of Parameters and Values!");
    }
    private void checkInvalidParam(string[] paramNames, SqlDbType[] paramTypes, object[] paramValues)
    {
        bool isValid = true;
        if (paramNames == null || paramTypes == null || paramValues == null) isValid = false;
        int N = Math.Min(paramNames.Length, paramTypes.Length);
        N = Math.Min(N, paramValues.Length);
        if (N == 0) isValid = false;
        if (!isValid)
        {
            throw new Exception("Database: Invalid Length of Parameters and Values!");
        }
    }
    private void checkInvalidParam(string[] paramNames, SqlDbType[] paramTypes, ArrayList paramValues)
    {
        if (paramNames == null || paramValues == null
            || paramNames.Length == 0 || paramValues.Count == 0)
        {
            throw new Exception("Database: Invalid Length of Parameters and Values!");
        }
    }
    public string MergeLogMessage()
    {
        StringBuilder sb = new StringBuilder();
        sb.AppendFormat("Database");
        if (this.command != null)
        {
            sb.AppendFormat("\nsql: {0}", this.command.CommandText);
            if (this.command.Parameters.Count > 0)
            {
                string sbParams = "\nParamNames(";
                string sbValues = "\nParamValues(";
                SqlParameter param = null;
                for (int i = 0; i < this.command.Parameters.Count; i++)
                {
                    param = this.command.Parameters[i];
                    if (param.Direction != ParameterDirection.ReturnValue)
                    {
                        sbParams += (i > 0 ? "," : "");
                        sbValues += (i > 0 ? "," : "");
                        sbParams += "@" + param.ParameterName;
                        sbValues += getValueParam(param.Value);
                    }
                }
                sbParams += ")";
                sbValues += ")";
                sb.Append(sbParams);
                sb.Append(sbValues);
            }
        }
        sb.Append("\n" + new string('-', 30));
        return sb.ToString();
    }

    private static string getValueParam(object obj)
    {
        if (obj == null || obj == DBNull.Value)
            return "null";
        if (obj is string)
            return "'" + obj + "'";
        return obj.ToString();
    }
    private bool IsEmpty(string s)
    {
        return s == null || s.Trim().Length == 0;
    }
    #endregion Utility

    #region Access of Dispose
    /// <summary>
    ///     Dispose of this object's resources.
    /// </summary>
    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(true);
    }

    /// <summary>
    ///		Free the instance variables of this object.
    /// </summary>
    protected virtual void Dispose(bool disposing)
    {
        try
        {
            if (!disposing)
                return; // we're being collected, so let the GC take care of this object

            if (connection != null && connection.State == ConnectionState.Open)
                connection.Close();
            if (command != null)
                command.Dispose();
        }
        catch { }
    }
    #endregion Access of Dispose

    private void ErrorProccess(Exception ex)
    {
        
        if (!this.ThrowException)
        {
            this._errorMessage = ex.Message;
            return;
        }
        //if (this.Debug == false)
        //{
        //    //this._errorMessage = ex.Message;
        //    FunctionsUtils.WriteLogMessage(ex);
        //    return;
        //}
        throw (ex);
    }
}